package com.ihg.hiex.admin.handler.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.ResourceUtils;

import com.ihg.hiex.admin.entity.dto.ChampionChange;
import com.ihg.hiex.admin.entity.dto.Competency;
import com.ihg.hiex.admin.entity.dto.DriveResult;
import com.ihg.hiex.admin.entity.dto.FinancialManagement;
import com.ihg.hiex.admin.entity.dto.LeadAndDevelop;
import com.ihg.hiex.admin.entity.dto.Swift;
import com.ihg.hiex.admin.entity.dto.WorkCollaboratively;
import com.ihg.hiex.admin.handler.ParseHandler;

/**
 * 
 * @author houtao
 *
 */
@Component("parseHandler")
public class ParseHandlerImpl implements ParseHandler {
    
    private static final Logger logger = LoggerFactory.getLogger(ParseHandlerImpl.class);
    
    @Value("#{config['cloud.path']}")
    private String cloudPath;
    
    @Value("#{cloud['cloud.host']}")
    private String cloudHost;
    
    /*
     * (non-Javadoc)
     * @see com.ihg.hiex.admin.handler.ParseHandler#parseWave(java.lang.String, java.lang.String)
     */
    public Competency parseWave(String delegateEmail, String wavePath) {
        
        Competency competency = null;
        
        try {
            
            DataFormatter formatter = new DataFormatter();
            Workbook workbook = WorkbookFactory.create(this.prepareFile(wavePath));
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rows = sheet.iterator();
            
            while (rows.hasNext()) {
                
                Row row = rows.next();
                int rowNum = row.getRowNum();
                
                if (rowNum <= 0) {
                    continue;
                }
                if(delegateEmail.equalsIgnoreCase(formatter.formatCellValue(row.getCell(2)))) {
                    DriveResult driveResult = this.generateDr(formatter, row);
                    WorkCollaboratively workCollaboratively = this.generateWc(formatter, row);
                    ChampionChange championChange = this.generateCc(formatter, row);
                    LeadAndDevelop leadAndDevelop = this.generateLd(formatter, row);
                    FinancialManagement financialManagement = this.generateFm(formatter, row);
                    
                    competency = new Competency(driveResult, championChange, leadAndDevelop, financialManagement, workCollaboratively);
                }
                
            }
            
            
        } catch (Exception e) {
            logger.error("##Exception when parse excel.", e);
        }
        
        return competency;
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    private DriveResult generateDr(DataFormatter formatter, Row row) {
        int directingPeople = Integer.valueOf(formatter.formatCellValue(row.getCell(34)));
        int managingTasks = Integer.valueOf(formatter.formatCellValue(row.getCell(48)));
        int producingOutput = Integer.valueOf(formatter.formatCellValue(row.getCell(50)));
        int takingAction = Integer.valueOf(formatter.formatCellValue(row.getCell(51)));
        int pursingGoals = Integer.valueOf(formatter.formatCellValue(row.getCell(53)));
        int generatingIdeas = Integer.valueOf(formatter.formatCellValue(row.getCell(24)));
        int developingStrategies = Integer.valueOf(formatter.formatCellValue(row.getCell(26)));
        int examingInformation = Integer.valueOf(formatter.formatCellValue(row.getCell(18)));
        int makingDecisions = Integer.valueOf(formatter.formatCellValue(row.getCell(33)));
        int showingComposure = Integer.valueOf(formatter.formatCellValue(row.getCell(37)));
        
        return new DriveResult(directingPeople, managingTasks, producingOutput, takingAction, pursingGoals, generatingIdeas, developingStrategies, examingInformation, makingDecisions, showingComposure);
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    private ChampionChange generateCc(DataFormatter formatter, Row row) {
        int generatingIdeas = Integer.valueOf(formatter.formatCellValue(row.getCell(24)));
        int convincingPeople = Integer.valueOf(formatter.formatCellValue(row.getCell(30)));
        int articulatingInformation = Integer.valueOf(formatter.formatCellValue(row.getCell(31)));
        int makingDecisions = Integer.valueOf(formatter.formatCellValue(row.getCell(33)));
        int empoweringIndividuals = Integer.valueOf(formatter.formatCellValue(row.getCell(35)));
        int embracingChange = Integer.valueOf(formatter.formatCellValue(row.getCell(42)));
        int exploringPossibilities = Integer.valueOf(formatter.formatCellValue(row.getCell(25)));
        int challengingIdeas = Integer.valueOf(formatter.formatCellValue(row.getCell(32)));
        
        return new ChampionChange(convincingPeople, articulatingInformation, empoweringIndividuals, embracingChange, generatingIdeas, exploringPossibilities, challengingIdeas, makingDecisions);
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    private LeadAndDevelop generateLd(DataFormatter formatter, Row row) {
        int directingPeople = Integer.valueOf(formatter.formatCellValue(row.getCell(34)));
        int convincingPeople = Integer.valueOf(formatter.formatCellValue(row.getCell(30)));
        int articulatingInformation = Integer.valueOf(formatter.formatCellValue(row.getCell(31)));
        int empoweringIndividuals = Integer.valueOf(formatter.formatCellValue(row.getCell(35)));
        int invitingFeedback = Integer.valueOf(formatter.formatCellValue(row.getCell(41)));
        int teamWorking = Integer.valueOf(formatter.formatCellValue(row.getCell(43)));
        int valuingIndividuals = Integer.valueOf(formatter.formatCellValue(row.getCell(44)));
        int managingTasks = Integer.valueOf(formatter.formatCellValue(row.getCell(48)));
        int interactingWithPeople = Integer.valueOf(formatter.formatCellValue(row.getCell(27)));
        int providingInsights = Integer.valueOf(formatter.formatCellValue(row.getCell(23)));
        
        return new LeadAndDevelop(convincingPeople, articulatingInformation, directingPeople, empoweringIndividuals, invitingFeedback, teamWorking, valuingIndividuals, managingTasks, interactingWithPeople, providingInsights);
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    private WorkCollaboratively generateWc(DataFormatter formatter, Row row) {
        int establishingRapport = Integer.valueOf(formatter.formatCellValue(row.getCell(28)));
        int convincingPeople = Integer.valueOf(formatter.formatCellValue(row.getCell(30)));
        int invitingFeedback = Integer.valueOf(formatter.formatCellValue(row.getCell(41)));
        int teamWorking = Integer.valueOf(formatter.formatCellValue(row.getCell(43)));
        int valuingIndividuals = Integer.valueOf(formatter.formatCellValue(row.getCell(44)));
        int interactingWithPeople = Integer.valueOf(formatter.formatCellValue(row.getCell(27)));
        int resolvingConflict = Integer.valueOf(formatter.formatCellValue(row.getCell(38)));
        
        return new WorkCollaboratively(interactingWithPeople, establishingRapport, convincingPeople, resolvingConflict, invitingFeedback, teamWorking, valuingIndividuals);
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    private FinancialManagement generateFm(DataFormatter formatter, Row row) {
        int providingInsights = Integer.valueOf(formatter.formatCellValue(row.getCell(23)));
        int generatingIdeas = Integer.valueOf(formatter.formatCellValue(row.getCell(24)));
        int seizingOpportunities = Integer.valueOf(formatter.formatCellValue(row.getCell(52)));
        int interpretingData = Integer.valueOf(formatter.formatCellValue(row.getCell(20)));
        int developingStrategies = Integer.valueOf(formatter.formatCellValue(row.getCell(26)));
        
        return new FinancialManagement(seizingOpportunities, interpretingData, providingInsights, generatingIdeas, developingStrategies);
    }

    /*
     * (non-Javadoc)
     * @see com.ihg.hiex.admin.handler.ParseHandler#parseSwift(java.lang.String, java.lang.String)
     */
    public Swift parseSwift(String delegateEmail, String swiftPath) {
        
        Swift swift = null;
        try {
            
            DataFormatter formatter = new DataFormatter();
            Workbook workbook = WorkbookFactory.create(this.prepareFile(swiftPath));
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rows = sheet.iterator();
            while (rows.hasNext()) {
                
                Row row = rows.next();
                int rowNum = row.getRowNum();
                
                if (rowNum <= 0) {
                    continue;
                }
                
                if(delegateEmail.equalsIgnoreCase(formatter.formatCellValue(row.getCell(1)))) {
                    int verbal = Integer.valueOf(formatter.formatCellValue(row.getCell(12)));
                    int numerical = Integer.valueOf(formatter.formatCellValue(row.getCell(15)));
                    int diagrammatic = Integer.valueOf(formatter.formatCellValue(row.getCell(18)));
                    swift = new Swift(verbal, numerical, diagrammatic);
                }
            }
            
        } catch (Exception e) {
            
            logger.error("##Exception when parse excel.", e);
        }
        
        return swift;
    }
    
    /**
     * request InputStream by path
     * 
     * @param path
     * @return
     */
    protected InputStream prepareFile(String path) {
        
        File file = null;
        
        InputStream inputStream = null;
        
        path = StringUtils.replace(path, cloudHost, cloudPath);
        
        try {
           
            file = ResourceUtils.getFile(path);
            
            inputStream = new FileInputStream(file);
        
        } catch (FileNotFoundException e) {
            
            logger.error("##Exception when find file.", e);
            
        }
        
        return inputStream;
    }
    
}
